﻿

using System;
using System.Data;

/// <summary>
/// Summary description for DirectServiceHome
/// </summary>
public class DirectServiceHome
{
    private  ConnectionBase sgbdr;
    public DirectServiceHome()
    {
        sgbdr = new ConnectionBase();
    } 
    public DataTable ShowSystem()
    {
        string _condition = " WHERE TYPEEQUIPMENT.TYPEEQUIPEMENTID = 1";
        string _order = " ORDER BY dbo.EQUIPEMENT.CODENUMBER";
        string  sql = "SELECT dbo.EQUIPEMENT.CODENUMBER, dbo.EQUIPEMENT.DESCRIPTIONEQUIPMENT, dbo.EMPLOYEE.EMPLOYEENAME " +
                ", TYPEEQUIPMENT.TYPEEQUIPMENT " +
                " FROM dbo.EQUIPEMENT INNER JOIN " +
                " dbo.OWN ON dbo.EQUIPEMENT.CODENUMBER = dbo.OWN.CODENUMBER INNER JOIN " +
                " dbo.EMPLOYEE ON dbo.OWN.EMPLOYEEID = dbo.EMPLOYEE.EMPLOYEEID INNER JOIN " +
                " EQUIPMENTPURCHASEINFORMATION ON EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID " +
                " INNER JOIN [HAVE] ON [HAVE].[HAVEID] = EQUIPMENTPURCHASEINFORMATION.HAVEID INNER JOIN " +
                " TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [HAVE].TYPEEQUIPEMENTID"
                + _condition + _order;	
      
        //string sql= "SELECT dbo.EQUIPEMENT.CODENUMBER,dbo.EQUIPEMENT.DESCRIPTIONEQUIPMENT,dbo.EMPLOYEE.EMPLOYEENAME" +
        //            ",TYPEEQUIPMENT.TYPEEQUIPMENT" + 
        //            "FROM dbo.EQUIPEMENT INNER JOIN" +
        //            "dbo.OWN ON dbo.EQUIPEMENT.CODENUMBER = dbo.OWN.CODENUMBER INNER JOIN" +  
        //            "dbo.EMPLOYEE ON dbo.OWN.EMPLOYEEID = dbo.EMPLOYEE.EMPLOYEEID INNER JOIN" + 
        //            "EQUIPMENTPURCHASEINFORMATION ON EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID" + 
        //            "INNER JOIN [HAVE] ON [HAVE].[HAVEID] = EQUIPMENTPURCHASEINFORMATION.HAVEID INNER JOIN" + 
        //            "TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [HAVE].TYPEEQUIPEMENTID" +
        //           "WHERE TYPEEQUIPMENT.TYPEEQUIPEMENTID = 1 ORDER BY dbo.EQUIPEMENT.CODENUMBER";
	   return sgbdr.consultSQL(sql).Tables[0];

    }
    public DataTable ShowEquipmentSupply()
    {
        string s = string.Empty;						
	    string _order = " ORDER BY dbo.EQUIPEMENT.CODENUMBER";
		string sql = "SELECT dbo.EQUIPEMENT.CODENUMBER, dbo.EQUIPEMENT.DESCRIPTIONEQUIPMENT, dbo.EMPLOYEE.EMPLOYEENAME " +
				", TYPEEQUIPMENT.TYPEEQUIPMENT " +
				" FROM dbo.EQUIPEMENT INNER JOIN " + 
				" dbo.OWN ON dbo.EQUIPEMENT.CODENUMBER = dbo.OWN.CODENUMBER INNER JOIN " + 
				" dbo.EMPLOYEE ON dbo.OWN.EMPLOYEEID = dbo.EMPLOYEE.EMPLOYEEID INNER JOIN " +
				" EQUIPMENTPURCHASEINFORMATION ON EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID " +
				" INNER JOIN [HAVE] ON [HAVE].[HAVEID] = EQUIPMENTPURCHASEINFORMATION.HAVEID INNER JOIN " +
                " TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [HAVE].TYPEEQUIPEMENTID  where dbo.EMPLOYEE.EMPLOYEEID=" + 2 +
                "AND Demaged = 0 AND OWN.Unuse=0" + "ORDER BY dbo.EQUIPEMENT.CODENUMBER";
        return sgbdr.consultSQL(sql).Tables[0]; 
    }

   public DataTable GetUserEquipmentCodde(int employeeId)
    {
        string _condition = " WHERE EMPLOYEE.EMPLOYEEID=" + employeeId;
        string _order = " ORDER BY dbo.EQUIPEMENT.CODENUMBER";
        string sql = "SELECT dbo.EQUIPEMENT.CODENUMBER, dbo.EQUIPEMENT.DESCRIPTIONEQUIPMENT, dbo.EMPLOYEE.EMPLOYEENAME " +
                ", TYPEEQUIPMENT.TYPEEQUIPMENT " +
                " FROM dbo.EQUIPEMENT INNER JOIN " +
                " dbo.OWN ON dbo.EQUIPEMENT.CODENUMBER = dbo.OWN.CODENUMBER INNER JOIN " +
                " dbo.EMPLOYEE ON dbo.OWN.EMPLOYEEID = dbo.EMPLOYEE.EMPLOYEEID INNER JOIN " +
                " EQUIPMENTPURCHASEINFORMATION ON EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID " +
                " INNER JOIN [HAVE] ON [HAVE].[HAVEID] = EQUIPMENTPURCHASEINFORMATION.HAVEID INNER JOIN " +
                " TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [HAVE].TYPEEQUIPEMENTID"
                + _condition + _order;
       return sgbdr.consultSQL(sql).Tables[0];
    }
}
  
